MySQL Query to Rank Employees by Salary

Rank Employees by Salary

Write a MySQL query to rank employees based on their salary in descending order using a window function.

Note: The RANK() window function is used in MySQL to assign a rank to each row within a result set based on specified ordering. It assigns the same rank to rows with identical values, but skips subsequent ranks, creating gaps in the ranking sequence. For example, if two rows are tied for second place, both will be given a rank of 2, and the next rank will be 4. This function is commonly used for ranking data like sales figures, scores, or performance metrics across different groups or partitions in a dataset.

Solution:

-- Select the EmployeeID column from the Employees table
SELECT 
    EmployeeID, 

    -- Select the Name column to display employee names
    Name, 

    -- Select the Salary column to show employee salaries
    Salary, 

    -- Use the RANK() window function to assign a rank to each employee based on salary
    -- ORDER BY Salary DESC ensures that the highest salary gets rank 1
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank 

-- Specify the table from which to retrieve the data
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to rank employees by their salary, with the highest salary receiving the top rank.
• This demonstrates the use of the RANK() window function to assign ranks based on a specific column.
• Key Components:
• RANK() OVER (ORDER BY Salary DESC): Assigns a rank to each employee based on their salary in descending order.
• SELECT EmployeeID, Name, Salary: Retrieves the employee details along with their rank.
• Why use Window Functions?:
• Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row.
• They are useful for ranking, partitioning, and aggregating data without collapsing the result set.
• Real-World Application:
• For example, in a company, you might want to rank employees by salary to determine compensation tiers or identify top earners.

For more Practice: Solve these Related Problems:

• Write a MySQL query to rank employees by salary, but assign the same rank to duplicate salaries without skipping ranks.
• Write a MySQL query to assign dense ranks to employees based on salary within each job title.
• Write a MySQL query to rank employees by salary but restart the ranking for each department.
• Write a MySQL query to find the second highest salary for each department.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Window Functions and CTEs Exercises Home
Next MySQL Exercise: Revoking INSERT Permission on a Table.